﻿Public Class ClsTMaxNumberOfVoucherDbAccess
    Inherits ClsBaseDbAccess
    '对应表：T_MaxNumberOfVoucher - 仅有一个字段：VoucherNumber 用来保存凭证连续号码（每个期间开始都置0，在结账后）

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_T_VOUCHER_MAXNUMBER)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub

    Public Function UpdateVoucherMaxNumber(ByVal p As String, ByVal maxNum As Integer) As Integer
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()
            Dim tmpParas(1) As OleDb.OleDbParameter
            Dim tmpReturn As Int32

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Update [" & Me.AppTableName & "]"
            tmpSql &= " set [VoucherNumber]= @VoucherNumber "
            tmpSql &= " Where [WorkPeriod]=@WorkPeriod "

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherNumber", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            '参数赋值
            tmpParas(0).Value = maxNum
            tmpParas(1).Value = p

            '插入并返回自动编号
            tmpReturn = tmpDbAccess.UpdateRecord(tmpSql, tmpParas) '.InsertData(tmpSql, tmpInsertParas, False)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTMaxNumberOfVoucherDbAccess - UpdateVoucherMaxNumber"

            Return 0
        End Try
    End Function

    Public Function InsertVoucherMaxNumber(ByVal tmpCls As ClsTMaxNumberOfVoucher) As Integer
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()
            Dim tmpInsertParas(1) As OleDb.OleDbParameter
            Dim tmpReturn As Int32

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Insert into [" & Me.AppTableName & "]"
            tmpSql &= " ([WorkPeriod],[VoucherNumber]) values("
            tmpSql &= "@WorkPeriod,@VoucherNumber "
            tmpSql &= ")"

            '构造参数集合
            tmpInsertParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpInsertParas(1) = New OleDb.OleDbParameter("@VoucherNumber", OleDb.OleDbType.VarWChar)
            '参数赋值
            tmpInsertParas(0).Value = tmpCls.WorkPeriod
            tmpInsertParas(1).Value = tmpCls.VoucherNumber

            '插入并返回自动编号
            tmpReturn = tmpDbAccess.InsertData(tmpSql, tmpInsertParas, False)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTMaxNumberOfVoucherDbAccess - InsertVoucherMaxNumber"

            Return 0
        End Try
    End Function

    Public Function GetVoucherMaxNumber(ByVal wp As String) As ClsTMaxNumberOfVoucher
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpRet As List(Of ClsTMaxNumberOfVoucher)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Select * from [" & Me.AppTableName & "] "
            tmpSql &= " Where [WorkPeriod] = @WorkPeriod "

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            tmpRet = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTMaxNumberOfVoucher))

            '关闭数据库
            tmpDbAccess.DbClose()

            If tmpRet.Count > 0 Then
                Return tmpRet.Item(0)
            Else
                Return Nothing
            End If

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTMaxNumberOfVoucherDbAccess - GetVoucherMaxNumber"

            Return Nothing
        End Try
    End Function

    Public Overrides Function ConvertRecordToClass(reader As OleDb.OleDbDataReader) As Object
        Dim tmpClsList As List(Of ClsTMaxNumberOfVoucher) = New List(Of ClsTMaxNumberOfVoucher)

        If reader Is Nothing Then
            Return tmpClsList
        End If


        While reader.Read()

            Dim tmpCls As ClsTMaxNumberOfVoucher = New ClsTMaxNumberOfVoucher()

            With tmpCls
                .WorkPeriod = reader.GetString(0)
                .VoucherNumber = reader.GetInt32(1)
            End With

            tmpClsList.Add(tmpCls)

        End While


        Return tmpClsList
    End Function
End Class
